from datetime import datetime, timedelta
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
import requests
import time
#订单取消

# 假设已有数据库连接配置
DATABASE_URL = "mysql+pymysql://chishi_erjiakj_c:raBtRNr21rfw2FjB@1.14.97.95/chishi_erjiakj_c"
engine = create_engine(DATABASE_URL)
Session = sessionmaker(bind=engine)
session = Session()

# 计算时间范围
cutoff_time = datetime.now() - timedelta(minutes=15)
formatted_date = cutoff_time.strftime('%Y-%m-%d %H:%M:%S')

try:
    # 执行主查询（带 JOIN）
    main_query = text("""
        SELECT `details`.`phone`,`details`.`id` as  details_id,`details`.`goods_id`,`godate`,`price`,goods.id goodsid FROM `sys_goods` `goods` 
        INNER JOIN `sys_goods_details` `details` ON `goods`.`id`=`details`.`goods_id` 
        WHERE  `details`.`update_by` = 1  AND `details`.`class` < 1  AND `details`.`update_date` < :date 
    """)

    details = session.execute(main_query, {'date': formatted_date}).fetchall()
    print(details)
    for det in details:
        # 检查用户是否存在（非机器人用户）
        user_check = text("""
            SELECT id 
            FROM sys_user 
            WHERE phone = :phone 
              AND robot = 1 
            LIMIT 1
        """)
        user_exists = session.execute(user_check, {'phone': det.phone}).fetchone()

        if not user_exists:
            # 发送短信（需实现 send_msg 函数）

            data = {'phone': det.phone, 'code': 'date', 'value': det.godate.strftime('%Y-%m-%d'),
                    'sms': 'SMS_474240509'}
            requests.post('https://chishi.erjiakj.cn/index/daily/alysms', data=data)
        # 更新 goods_details
        update_details = text("""
            UPDATE sys_goods_details
            SET update_by = 0, 
                update_date = :datetime
            WHERE id = :details_id
        """)
        session.execute(update_details, {'datetime':time.strftime("%Y-%m-%d %H:%M:%S"),'details_id': det.details_id})

        # 检查空价格并更新 goods
        if not det.price:  # 根据实际数据类型可能需要调整判断逻辑
            update_goods = text("""
                UPDATE sys_goods
                SET update_by = 0,
                    update_date = :datetime
                WHERE id = :goodsid
            """)
            session.execute(update_goods, {'datetime':time.strftime("%Y-%m-%d %H:%M:%S"),'goodsid': det.goodsid})

    session.commit()
    print('15分钟订单取消成功')
except Exception as e:
    session.rollback()
    print(f"Error occurred: {str(e)}")
    raise
finally:
    session.close()